import openpyxl
import datetime
from openpyxl.styles import Alignment

# 原始文件
sourceFile = './resources/source/extract/source.xlsx'

# 目标文件  直接在当前文件上修改后保存
targetFile = './resources/source/extract/target.xlsx'

# 要处理的sheet名和列名
columnMapping = {'现货': ['购销', '对冲类型', '是否开票', '出入库日期'], '期货': ['头寸号合约', '净敞口元素', '期货种类']}

# 源表和目标表sheet名的映射
sheetMapping = {'现货': '一、二现货'}

# 单元格日期格式
NUMBER_FORMAT = 'yyyy-mm-dd'


def load_file(file_path, data_only_flag):
	try:
		return openpyxl.load_workbook(file_path, data_only=data_only_flag)
	except Exception as e:
		raise Exception('读取文件失败,请查看路径是否正确:' + file_path)


# 获取某行所有值
def get_row_value(ws, row):
	columns = ws.max_column
	row_data = []
	for i in range(1, columns + 1):
		cell_value = ws.cell(row=row, column=i).value
		row_data.append(cell_value)
	return row_data


# 替换表格的某列
def replace_column_value(source_ws, target_ws, column_name):
	source_column_index = -1
	target_column_index = -1

	for i in range(1, source_ws.max_column + 1):
		cell_value = source_ws.cell(row=1, column=i).value
		if cell_value == column_name:
			source_column_index = i

	for k in range(1, target_ws.max_column + 1):
		cell_value = target_ws.cell(row=1, column=k).value
		if cell_value == column_name:
			target_column_index = k

	for j in range(1, source_ws.max_row + 1):
		cell_value = source_ws.cell(row=j, column=source_column_index).value
		# 将日期设置为‘yyyy-mm-dd格式,且水平、垂直居中
		if type(cell_value) == datetime.datetime:
			target_ws.cell(row=j, column=target_column_index).number_format = NUMBER_FORMAT
			target_ws.cell(row=j, column=target_column_index).alignment = Alignment(horizontal='center', vertical='center')

		target_ws.cell(row=j, column=target_column_index).value = cell_value


def get_mapping_sheet_name(sheet_name):
	return sheet_name if sheet_name not in sheetMapping.keys() else sheetMapping[sheet_name]


# 循环遍历表格
def run():
	print("[Running] 开始执行表格列替换 ...")
	for sheet_name in columnMapping:
		print('===========================================')
		source_ws = source_workbook[sheet_name]
		target_sheet_name = get_mapping_sheet_name(sheet_name)
		target_ws = target_workbook[target_sheet_name]
		for column_name in columnMapping[sheet_name]:
			replace_column_value(source_ws, target_ws, column_name)
			print('[SUCCESS] 替换【%s】表的列【%s】完成' % (sheet_name, column_name))

	target_workbook.save(targetFile)
	print('===========================================')


# 校验格式是否正确
def check(file_path, is_target_sheet):
	workbook = load_file(file_path, not is_target_sheet)
	sheet_names = workbook.sheetnames
	for sheet_name in columnMapping:
		target_columns = columnMapping[sheet_name]
		if is_target_sheet:
			sheet_name = get_mapping_sheet_name(sheet_name)
		if sheet_name not in sheet_names:
			error = '【error】文件:' + file_path + ' 中找不到sheet表:' + sheet_name
			raise Exception(error)
		else:
			sheet = workbook[sheet_name]
			first_column = get_row_value(sheet, 1)
			for target_column in target_columns:
				if target_column not in first_column:
					error = '【error】文件:' + file_path + ' 的表:' + sheet_name + ' 中找不到对应的列:' + target_column
					raise Exception(error)
	print('[Success] 文件: ' + file_path + ' 格式校验通过')
	return workbook


if __name__ == '__main__':
	print("[Running] 正在检查您的excel文件格式是否正确 ...")
	source_workbook = check(sourceFile, False)
	target_workbook = check(targetFile, True)
	run()
	print("\n[Done] 恭喜您！任务全部执行完成！您可以去【%s】路径下去查看您的最新文件！" % targetFile)
